use ServerMonitor
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_JobStepStatus_JobStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[JobStepStatus] DROP CONSTRAINT FK_JobStepStatus_JobStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JobStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobStepStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JobStepStatus]
GO

CREATE TABLE [dbo].[JobStatus] (
	[Id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[JobName] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
	[Status] [bit] NULL ,
	[ExecutionTime] [datetime] NULL ,
	[NextRunTime] [datetime] NULL ,
	[DurationTime] [int] NULL ,
	[UpdateTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JobStepStatus] (
	[Id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[JobStatusId] [bigint] NOT NULL ,
	[Name] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
	[StepId] [tinyint] NULL ,
	[ExecutionTime] [datetime] NULL ,
	[DurationTime] [int] NULL ,
	[Status] [bit] NULL ,
	[Message] [nvarchar] (3000) COLLATE Chinese_PRC_CI_AS NULL ,
	[UpdateTime] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobStatus] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[JobStepStatus] WITH NOCHECK ADD 
	CONSTRAINT [PK_JobStepStatus] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_JobTime] ON [dbo].[JobStatus]([ExecutionTime], [JobName]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobStepStatus] ADD 
	CONSTRAINT [DF__JobStepSt__Statu__7D78A4E7] DEFAULT (0) FOR [Status],
	CONSTRAINT [DF__JobStepSt__Updat__7E6CC920] DEFAULT (getdate()) FOR [UpdateTime]
GO

 CREATE  INDEX [IX_JobStepStatus] ON [dbo].[JobStepStatus]([JobStatusId]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobStepStatus] ADD 
	CONSTRAINT [FK_JobStepStatus_JobStatus] FOREIGN KEY 
	(
		[JobStatusId]
	) REFERENCES [dbo].[JobStatus] (
		[Id]
	)
GO
